package com.rhb.erp.hr.department.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import com.rhb.erp.hr.department.bean.*;
import com.rhb.util.UID;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.apache.log4j.Logger;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.w3c.dom.*;

public class DepartmentDAO_HibernateImpl extends HibernateDaoSupport implements
		DepartmentDAO {
	static Logger logger = Logger.getLogger(DepartmentDAO_HibernateImpl.class);

	public void delete(Department obj) {
		try {
			this.getHibernateTemplate().delete(obj);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public Department findByPK(String pk) {
		return (Department) this.getHibernateTemplate().get(Department.class,
				pk);
	}

	public String create(Department obj) {
		if(obj.getDepartmentPk()==null || "".equals(obj.getDepartmentPk())){
			obj.setDepartmentPk(UID.getUID());			
		}
		String pk = obj.getDepartmentPk();
		try {
			this.getHibernateTemplate().save(obj);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return pk;
	}

	public void update(Department obj) {
		try {
			this.getHibernateTemplate().update(obj);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private Query getQuery(String str, DepartmentQuery quester,String orderBy) {
		StringBuffer hql = new StringBuffer(str);
		if (quester != null) {
			if (quester.getParentPk() != null && !"".equals(quester.getParentPk())) {
				hql.append(" and parent = :parent");
			}
			if (quester.getNameMap() != null && !"".equals(quester.getNameMap())) {
				hql.append(" and nameMap like :nameMap");
			}
		}
		if(orderBy!=null && !"".equals(orderBy)){
			hql.append(orderBy);
		}

		Query query = this.getSession().createQuery(hql.toString());
		if (quester != null) {
			if (quester.getParentPk() != null && !"".equals(quester.getParentPk())) {
				query.setString("parent", quester.getParentPk());
			}
			if (quester.getNameMap() != null && !"".equals(quester.getNameMap())) {
				query.setString("nameMap", quester.getNameMap());
			}
		}
		return query;
	}

	public int getAllCount_findByQuery(DepartmentQuery quester) {
		Long allcount = new Long(0);
		String hql = "select count(department) from Department department where 1=1";
		try {
			Query query = getQuery(hql, quester, null);
			Iterator result = query.iterate();
			if (result.hasNext()) {
				allcount = (Long) result.next();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return allcount.intValue();
	}

	public List findByQuery(DepartmentQuery quester, int start, int count) {
		String hql = "select department from Department department where 1=1";
		String orderBy = " order by nameMap";
		try {
			Query query = getQuery(hql, quester, orderBy);
			query.setFirstResult(start);
			query.setMaxResults(count);

			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public Document getDepartmentXMLDoc() throws ParserConfigurationException {
		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder db = dbf.newDocumentBuilder();
		Document document = db.newDocument();

		Element e = document.createElement("department");
		e.setAttribute("name", "");
		e.setAttribute("pk", "");

		List tops = findTops();
		if(tops!=null){
			for(int i=0; i<tops.size(); i++){
				Department department = (Department)tops.get(i);
				Element element = getDepartmentElement(document,department);
				e.appendChild(element);
			}
		}

		document.appendChild(e);

		return document;
	}
	
	private Element getDepartmentElement(Document document,Department department) {
		
		Element element = document.createElement("department");
		element.setAttribute("name", department.getDepartmentName());
		element.setAttribute("pk", department.getDepartmentPk());
		
		Iterator it = department.getChildren().iterator();
		while(it.hasNext()) {
			Element e = getDepartmentElement(document,(Department)it.next());
			element.appendChild(e);
		}
		return element;
	}

	private List findTops() {
		String str = "select department from Department department where parent is null order by nameMap";
		Query query = this.getSession().createQuery(str);
		try{
			return query.list();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public String getTreeMap(String departmentPk){
		String treeMap = null;
		if(departmentPk!=null && !"".equals(departmentPk)){
			treeMap = generateTreeMap("",this.findByPK(departmentPk));			
		}
		return treeMap;
	}
	
	private String generateTreeMap(String treeMap,Department department){
		if(department != null){
			treeMap += department.getDepartmentPk() + ",";
			//System.out.println("*********   treeMap='" + treeMap + "'");
			if(department.getParent()!= null){
				treeMap += generateTreeMap(treeMap,department.getParent());
			}
		}
		return treeMap;
	}
	
	public int updateNameMap(String oldNameMap,String newNameMap) throws SQLException{
		int i = 0;
		String sql = "update department set nameMap=replace(nameMap,'"+oldNameMap+"','"+newNameMap+"') where nameMap like '"+oldNameMap+"%'";
		//logger.info("********* " + sql + "***********");
		Connection con = null;
		Statement st = null;
		try {
			con = this.getSession().connection();
			st = con.createStatement();
			i = st.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(st != null){
				st.close();
			}
			if(con != null){
				con.close();
			}
		}
		return i;
	}

}
